--    Author    : ChenErHao
--    Name      : DM.MID_T_MTH_END_M_AMONT.HQL
--    Functions : 利率-按月末存量计算_MID2
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2019-01-24  ChenErHao       1.CREATE THE PROCEDURE
--

INSERT OVERWRITE TABLE DM.MID_T_MTH_END_M_AMONT  PARTITION (FISCAL_MTH_DIMNSN_ID = '#V_DATA_MONTH#') 
SELECT 

 A.DATA_AREA_CODE, 
 SUM(DEP_BALANCE), 
 SUM(DEP_BALANCE_AMT), 
 SUM(LOAN_BALANCE), 
 SUM(LOAN_BALANCE_AMT), 
 SUM(DEP_BALANCE_L), 
 SUM(DEP_BALANCE_AMT_L), 
 SUM(LOAN_BALANCE_L), 
 SUM(LOAN_BALANCE_AMT_L),
 SUM(WARL),
 '#V_DATA_MONTH#' AS P_MONTH
  FROM (select 
         DATA_AREA_CODE, 
           DEP_BALANCE, 
           DEP_BALANCE_AMT, 
           LOAN_BALANCE, 
           LOAN_BALANCE_AMT, 
           DEP_BALANCE_L, 
           DEP_BALANCE_AMT_L, 
           LOAN_BALANCE_L, 
           LOAN_BALANCE_AMT_L FROM DM.MID_T_MTH_END_M_AMONT_ONE WHERE DATA_MONTH='#V_DATA_MONTH#') A 
  LEFT JOIN (
        select 
            AREA_NO_ID,
            CASE WHEN SUM(LOAN_BALANCE) IS NULL THEN 0
                 WHEN SUM(LOAN_BALANCE) ='' THEN 0
                 ELSE round(SUM(LOAN_BALANCE_AMT)*1.000 /SUM(LOAN_BALANCE),4) END
                 -
            CASE WHEN SUM(DEP_BALANCE) IS NULL THEN 0
                 WHEN SUM(DEP_BALANCE) ='' THEN 0
                 ELSE round(SUM(DEP_BALANCE_AMT)*1.0000 /SUM(DEP_BALANCE),4) END WARL
    from DM.T_MTH_END_M_AMONT
  WHERE 
        FISCAL_MTH_DIMNSN_ID BETWEEN '#V_LAST_13M_END_MONTH#' AND '#V_LAST_1M_END_MONTH#' and  AREA_NO_ID!=-1
  GROUP BY 
        AREA_NO_ID ) B ON A. DATA_AREA_CODE= B.AREA_NO_ID where A. DATA_AREA_CODE!=-1 
  GROUP BY 
        A.DATA_AREA_CODE 
;